﻿using System.Linq;
using System.Data.Linq;
using UniMana.Utils.Controls;
using System;
using UniMana.Data;
using System.Collections.Generic;
namespace UniMana.Data
{
    public static class Training_StudentData
    {
        /// <summary>
        /// danh sách các sinh viên học lop hoc phan
        /// </summary>
        /// <param name="subjectClassKey"></param>
        /// <param name="student_Class"></param>
        /// <param name="student"></param>
        /// <param name="student_Subject_Class"></param>
        /// <returns></returns>
        public static IQueryable
            GetBySubjectClassKey(this Table<Training_Student> student, 
                            Table<Training_Student_Subject_Class> student_Subject_Class, 
                            Table<Training_Student_Class> student_Class, 
                            int subjectClassKey)
        {
            var Student = from st_c in student_Class
                          join st in student on st_c.Training_StudentKey equals st.Training_StudentKey
                          join sts in student_Subject_Class on st.Training_StudentKey equals sts.Training_StudentKey
                          where sts.Training_Subject_ClassKey == subjectClassKey
                          select new
                          {
                                st.Training_StudentKey,
                                st.Address,
                                st.Birthday,
                                st.CellPhone,
                                st.HomePhone,
                                st.LastName,
                                st.Note,
                                st.SureName,
                                sts.Training_TrainingProgramKey,
                                Training_Student_Subject_Regist_TypeKey = 
                                            sts.Training_Student_Subject_Regist_TypeKey == 0 ? "Học lần đầu" : 
                                            sts.Training_Student_Subject_Regist_TypeKey == 1 ? "Học lại" : "Cải thiện"
                          };
            return Student.Distinct();
        }        
        /// <summary>
        /// Lấy danh sách sinh viên chưa học một môn trong học kỳ, năm học
        /// </summary>
        /// <param name="student"></param>
        /// <param name="student_Subject"></param>
        /// <param name="training_Student_Subject_Class"></param>
        /// <param name="subjectKey"></param>
        /// <param name="termKey"></param>
        /// <param name="schoolYearKey"></param>
        /// <returns></returns>
        public static IQueryable GetBySbuject_Term_SchoolYear(this Table<Training_Student> student,
                                   Table<Training_Student_Subject> student_Subject,
                                   Table<Training_Student_Subject_Class> training_Student_Subject_Class,
                                   Table<Training_Subject_Class> training_Subject_Class,
                                   Table<Training_Subject_Open> training_Subject_Open,
                                   int subjectKey)
        {
            var student_ = from st in student
                           join st_sj in student_Subject on st.Training_StudentKey equals st_sj.Training_StudentKey
                           join sj_o in training_Subject_Open on st_sj.Training_SubjectOpenKey equals sj_o.Training_SubjectOpenKey
                           where
                             st_sj.Training_SubjectKey == subjectKey &&
                             sj_o.Training_SubjectKey == subjectKey &&
                             (sj_o.Closed == false || sj_o.Closed == null)
                             &&
                             !((from st_sjc in training_Student_Subject_Class
                                join sjc in training_Subject_Class on st_sjc.Training_Subject_ClassKey equals sjc.Training_Subject_ClassKey
                                where
                                  sjc.Training_SubjectKey == subjectKey &&
                                  sj_o.Training_SubjectOpenKey == st_sjc.Training_SubjectOpenKey
                                //&& sjc.Catalogue_TermKey == termKey &&
                                //sjc.Catalogue_SchoolYearKey == schoolYearKey
                                select st_sjc.Training_StudentKey).Contains((Guid)st_sj.Training_StudentKey))
                           select new
                           {
                               st.Training_StudentKey,
                               st.Address,
                               st.Birthday,
                               st.CellPhone,
                               st.HomePhone,
                               st.LastName,
                               st.Note,
                               st.SureName,
                               st_sj.Training_TrainingProgramKey,
                               Training_Student_Subject_Regist_TypeKey =
                                            st_sj.Training_Student_Subject_Regist_TypeKey == 0 ? "Học lần đầu" :
                                            st_sj.Training_Student_Subject_Regist_TypeKey == 1 ? "Học lại" : "Cải thiện"
                           };
            return student_.Distinct();
        }


        #region [Thông tin sinh viên]
        /// <summary>
        /// Lấy thông tin sinh viên theo Id gần giống, 
        /// !!! _iDepartmentKey là dùng để phân quyền
        /// </summary>
        /// <param name="nTraning_Student">Training_Student</param>
        /// <param name="nTrainingStudent_Class">Training_Student_Class</param>
        /// <param name="_strID">Mã sinh viên</param>
        /// <returns>IQueryable</returns>
        public static IQueryable GetListLikeID(this Table<Training_Student> nTraning_Student, Table<Training_Student_Class> nTrainingStudent_Class, string _strID
                                               , int _iDeparmentKey = -1)
        {
            var studentList = from std_cls in nTrainingStudent_Class
                              join std in nTraning_Student on std_cls.Training_StudentKey equals std.Training_StudentKey
                              where std_cls.ID.Contains(_strID)
                              where std_cls.Mainly == true
                              orderby std_cls.ID
                              select new
                              {
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_StudentKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone
                              };

            if (_iDeparmentKey != -1 && _iDeparmentKey != 0)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }
            return studentList;
        }


        /// <summary>
        /// Lấy thông tin sinh viên theo Id gần giống
        /// </summary>
        /// <param name="nTraning_Student">Training_Student</param>
        /// <param name="nTrainingStudent_Class">Training_Student_Class</param>
        /// <param name="_strID">Mã sinh viên</param>
        /// <returns>IQueryable</returns>
        public static IQueryable GetListLikeName(this Table<Training_Student> nTraning_Student, Table<Training_Student_Class> nTrainingStudent_Class, string _strName
                                               , int _iDeparmentKey = -1)
        {
            var studentList = from std_cls in nTrainingStudent_Class
                              join std in nTraning_Student on std_cls.Training_StudentKey equals std.Training_StudentKey
                              //where std.LastName.Contains(_strName) || std.SureName.Contains(_strName)
                              where std_cls.Mainly == true
                              orderby std_cls.ID
                              select new
                              {
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_StudentKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  FullName = " " + std.LastName + " " + std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone
                              };
            studentList = studentList.Where(p => p.FullName.EndsWith(" " + _strName));

            if (_iDeparmentKey != -1 && _iDeparmentKey != 0)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }
            return studentList;
        }

        /// <summary>
        /// Lấy thông tin sinh viên theo các khóa ngoại
        /// </summary>
        /// <param name="nTraining_Student">Bảng Sinh viên</param>
        /// <param name="nTraining_Student_Class">Bảng Sinh viên - Lớp</param>
        /// <param name="_iLevelKey">Bậc đào tạo Key</param>
        /// <param name="_iTrainingTypeKey">Loại hình đào tạo Key</param>
        /// <param name="_iCourseKey">Khóa học Key</param>
        /// <param name="_iMajorKey">Chuyên ngành Key</param>
        /// <param name="_iStatusKey">Tình trạng Key</param>
        /// <returns>IQueryable</returns>
        public static IQueryable GetListByRef(this Table<Training_Student> nTraining_Student, Table<Training_Student_Class> nTraining_Student_Class,

            int _iLevelKey = -1, int _iTrainingTypeKey = -1, int _iCourseKey = -1, int _iDeparmentKey = -1, int _iMajorKey = -1, int _iStatusKey = -1,
            int _iClassKey = -1)
        {
            var studentList = from std_cls in nTraining_Student_Class
                              join std in nTraining_Student on std_cls.Training_StudentKey equals std.Training_StudentKey
                              where std_cls.Mainly == true
                              orderby std_cls.ID
                              select new
                              {
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_StudentKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone,
                              };

            if (_iLevelKey != -1) { studentList = studentList.Where(p => p.Catalogue_LevelKey == _iLevelKey); }
            if (_iTrainingTypeKey != -1) { studentList = studentList.Where(p => p.Catalogue_TrainingTypeKey == _iTrainingTypeKey); }
            if (_iCourseKey != -1) { studentList = studentList.Where(p => p.Catalogue_CourseKey == _iCourseKey); }
            if (_iMajorKey != -1) { studentList = studentList.Where(p => p.Catalogue_MajorKey == _iMajorKey); }
            if (_iClassKey != -1) { studentList = studentList.Where(p => p.Training_ClassKey == _iClassKey); }
            if (_iStatusKey != -1) { studentList = studentList.Where(p => p.Catalogue_StatusKey == _iStatusKey); }
            if (_iDeparmentKey != -1 && _iMajorKey == -1)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }
            return studentList;
        }
        #endregion

        #region [Thông tin thiếu]

        public static IQueryable GetListMissInfoLikeName(this Table<Training_Student> nTraning_Student, Table<Training_Student_Class> nTrainingStudent_Class, string _strName
                                       , int _iDeparmentKey = -1)
        {
            var studentList = from std_cls in nTrainingStudent_Class
                              join std in nTraning_Student on std_cls.Training_StudentKey equals std.Training_StudentKey
                              //where std.LastName.Contains(_strName) || std.SureName.Contains(_strName)
                              where std_cls.Mainly == true
                              where std_cls.Catalogue_LevelKey == null
                              where std_cls.Catalogue_TrainingTypeKey == null
                              where std_cls.Catalogue_CourseKey == null
                              where std_cls.Catalogue_MajorKey == null
                              where std_cls.Catalogue_StatusKey == null
                              where std_cls.Training_StudentKey == null
                              where std_cls.Training_ClassKey == null
                              where std_cls.ID == "" || std_cls.ID == null
                              where std.SureName == "" || std.SureName == null
                              where std.LastName == "" || std.LastName == null
                              where std.Birthday == null
                              where std.Gender == null
                              where std.PlaceOfBirth == "" || std.PlaceOfBirth == null
                              where std.CellPhone == "" || std.CellPhone == null
                              where std.HomePhone == "" || std.HomePhone == null
                              orderby std_cls.ID
                              select new
                              {
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_StudentKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  FullName = " " + std.LastName + " " + std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone
                              };
            studentList = studentList.Where(p => p.FullName.EndsWith(" " + _strName));

            if (_iDeparmentKey != -1 && _iDeparmentKey != 0)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }
            return studentList;
        }

        public static IQueryable GetListMissInfoLikeID(this Table<Training_Student> nTraning_Student, Table<Training_Student_Class> nTrainingStudent_Class, string _strID
                                       , int _iDeparmentKey = -1)
        {
            var studentList = from std_cls in nTrainingStudent_Class
                              join std in nTraning_Student on std_cls.Training_StudentKey equals std.Training_StudentKey
                              where std_cls.ID.Contains(_strID)
                              where std_cls.Mainly == true
                              where std_cls.Catalogue_LevelKey == null
                              where std_cls.Catalogue_TrainingTypeKey == null
                              where std_cls.Catalogue_CourseKey == null
                              where std_cls.Catalogue_MajorKey == null
                              where std_cls.Catalogue_StatusKey == null
                              where std_cls.Training_StudentKey == null
                              where std_cls.Training_ClassKey == null
                              where std_cls.ID == "" || std_cls.ID == null
                              where std.SureName == "" || std.SureName == null
                              where std.LastName == "" || std.LastName == null
                              where std.Birthday == null
                              where std.Gender == null
                              where std.PlaceOfBirth == "" || std.PlaceOfBirth == null
                              where std.CellPhone == "" || std.CellPhone == null
                              where std.HomePhone == "" || std.HomePhone == null

                              orderby std_cls.ID
                              select new
                              {
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_StudentKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone
                              };

            if (_iDeparmentKey != -1 && _iDeparmentKey != 0)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }
            return studentList;
        }

        public static IQueryable GetListMissInfoByRef(this Table<Training_Student> nTraining_Student, Table<Training_Student_Class> nTraining_Student_Class,

             int _iLevelKey = -1, int _iTrainingTypeKey = -1, int _iCourseKey = -1, int _iDeparmentKey = -1, int _iMajorKey = -1, int _iStatusKey = -1,
             int _iClassKey = -1)
        {
            var studentList = from std_cls in nTraining_Student_Class
                              join std in nTraining_Student on std_cls.Training_StudentKey equals std.Training_StudentKey
                              where std_cls.Mainly == true
                              where std_cls.Catalogue_LevelKey == null
                              where std_cls.Catalogue_TrainingTypeKey == null
                              where std_cls.Catalogue_CourseKey == null
                              where std_cls.Catalogue_MajorKey == null
                              where std_cls.Catalogue_StatusKey == null
                              where std_cls.Training_StudentKey == null
                              where std_cls.Training_ClassKey == null
                              where std_cls.ID == "" || std_cls.ID == null
                              where std.SureName == "" || std.SureName == null
                              where std.LastName == "" || std.LastName == null
                              where std.Birthday == null
                              where std.Gender == null
                              where std.PlaceOfBirth == "" || std.PlaceOfBirth == null
                              where std.CellPhone == "" || std.CellPhone == null
                              where std.HomePhone == "" || std.HomePhone == null

                              orderby std_cls.ID
                              select new
                              {
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_StudentKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone,
                              };

            if (_iLevelKey != -1) { studentList = studentList.Where(p => p.Catalogue_LevelKey == _iLevelKey); }
            if (_iTrainingTypeKey != -1) { studentList = studentList.Where(p => p.Catalogue_TrainingTypeKey == _iTrainingTypeKey); }
            if (_iCourseKey != -1) { studentList = studentList.Where(p => p.Catalogue_CourseKey == _iCourseKey); }
            if (_iMajorKey != -1) { studentList = studentList.Where(p => p.Catalogue_MajorKey == _iMajorKey); }
            if (_iClassKey != -1) { studentList = studentList.Where(p => p.Training_ClassKey == _iClassKey); }
            if (_iStatusKey != -1) { studentList = studentList.Where(p => p.Catalogue_StatusKey == _iStatusKey); }
            if (_iDeparmentKey != -1 && _iMajorKey == -1)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }


            return studentList;
        }

        #endregion

        public static IQueryable GetSubjectClassFail(this Table<Training_Class> nClass,
                                                     Table<Training_Student_Class> nTraining_Student_Class,
                                                     Table<Training_Student> nTraining_Student,
                                                     Table<Training_Subject_Class> nTraining_Subject_Class,
                                                     Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,
                                                     Table<Training_Score_Transfer> nTraining_Score_Transfer,
                                                     Table<Training_Score> nTraining_Score,
                                                     int _iLevelKey = -1,
                                                     int _iTrainingTypeKey = -1,
                                                     int _iCourseKey = -1,
                                                     int _iDeparmentKey = -1,
                                                     int _iMajorKey = -1,
                                                     int _iClassKey = -1)
        {
            var Training_Score = (from training_Score in nTraining_Score
                                  select training_Score.Training_Subject_ClassKey).Distinct();

            var ListStudent = from Class in nTraining_Student_Class
                              join Student in nTraining_Student on Class.Training_StudentKey equals Student.Training_StudentKey
                              join TClass in nClass on Class.Training_ClassKey equals TClass.Training_ClassKey
                              where Class.Mainly == true && Class.Catalogue_StatusKey == 1

                              select new
                              {
                                  Student.Training_StudentKey,
                                  Class.ID,
                                  Student.LastName,
                                  Student.SureName,
                                  Gender = Student.Gender == UniMana.Utils.Configs.DefaultConfig.FEMALE ? "Nữ" : "Nam",
                                  Birthday = Student.ExceptionBirthday != null ? Student.ExceptionBirthday.ToString() : Student.Birthday.Value.Day + "/" + Student.Birthday.Value.Month + "/" + Student.Birthday.Value.Year,
                                  Student.CellPhone,
                                  Student.Email,
                                  Class.Training_TrainingProgramKey,
                                  Class.Catalogue_LevelKey,
                                  Class.Catalogue_TrainingTypeKey,
                                  Class.Catalogue_CourseKey,
                                  Class.Catalogue_MajorKey,
                                  Class.Training_ClassKey,
                                  TClass.Yearly,
                                  //TClass.Training_ClassKey

                              };

            if (_iLevelKey != -1) { ListStudent = ListStudent.Where(p => p.Catalogue_LevelKey == _iLevelKey); }
            if (_iTrainingTypeKey != -1) { ListStudent = ListStudent.Where(p => p.Catalogue_TrainingTypeKey == _iTrainingTypeKey); }
            if (_iCourseKey != -1) { ListStudent = ListStudent.Where(p => p.Catalogue_CourseKey == _iCourseKey); }
            if (_iMajorKey != -1) { ListStudent = ListStudent.Where(p => p.Catalogue_MajorKey == _iMajorKey); }
            if (_iClassKey != -1) { ListStudent = ListStudent.Where(p => p.Training_ClassKey == _iClassKey); }
            if (_iDeparmentKey != -1 && _iMajorKey == -1)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                ListStudent = from list in ListStudent
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }



            var ListScore = from listStudent in ListStudent
                            join studentSubjectClass in nTraining_Student_Subject_Class on listStudent.Training_StudentKey equals studentSubjectClass.Training_StudentKey
                            where listStudent.Training_TrainingProgramKey == studentSubjectClass.Training_TrainingProgramKey
                            select new
                            {
                                listStudent.Training_StudentKey,
                                listStudent.ID,
                                listStudent.LastName,
                                listStudent.SureName,
                                listStudent.Gender,
                                listStudent.Birthday,
                                listStudent.CellPhone,
                                listStudent.Email,
                                listStudent.Training_TrainingProgramKey,
                                studentSubjectClass.Training_Subject_ClassKey,
                                studentSubjectClass.AverageScoreFinal,
                                listStudent.Yearly,
                                listStudent.Training_ClassKey,
                                listStudent.Catalogue_MajorKey
                            }; //***

            var ListStudentAndScore = from listScore in ListScore
                                      join subjectClass in nTraining_Subject_Class on listScore.Training_Subject_ClassKey equals subjectClass.Training_Subject_ClassKey
                                      where Training_Score.Contains(subjectClass.Training_Subject_ClassKey)
                                      select new
                                      {
                                          listScore.Training_StudentKey,
                                          listScore.ID,
                                          listScore.LastName,
                                          listScore.SureName,
                                          listScore.Gender,
                                          listScore.Birthday,
                                          listScore.CellPhone,
                                          listScore.Email,
                                          listScore.Training_TrainingProgramKey,
                                          listScore.Training_Subject_ClassKey,
                                          subjectClass.Training_SubjectKey,
                                          listScore.AverageScoreFinal,
                                          listScore.Yearly,
                                          listScore.Training_ClassKey,
                                          listScore.Catalogue_MajorKey
                                      };

            var ScoreTransfer = from listStudent in ListStudent
                                join scoreTransfer in nTraining_Score_Transfer on listStudent.Training_StudentKey equals scoreTransfer.Training_StudentKey
                                where listStudent.Training_TrainingProgramKey == scoreTransfer.Training_TrainingProgramKeyNew
                                select new
                                {
                                    listStudent.Training_StudentKey,
                                    listStudent.ID,
                                    listStudent.LastName,
                                    listStudent.SureName,
                                    listStudent.Gender,
                                    listStudent.Birthday,
                                    listStudent.CellPhone,
                                    listStudent.Email,
                                    listStudent.Training_TrainingProgramKey,
                                    scoreTransfer.Training_Subject_ClassKey,
                                    Training_TrainingProgramKeyOld = scoreTransfer.Training_TrainingProgramKey,
                                    listStudent.Yearly,
                                    listStudent.Training_ClassKey,
                                    listStudent.Catalogue_MajorKey
                                };

            var ListStudentAndScore1 = from scoreTransfer in ScoreTransfer
                                       join studentSubjectClass in nTraining_Student_Subject_Class on scoreTransfer.Training_StudentKey equals studentSubjectClass.Training_StudentKey
                                       join subjectClass in nTraining_Subject_Class on scoreTransfer.Training_Subject_ClassKey equals subjectClass.Training_Subject_ClassKey
                                       where scoreTransfer.Training_Subject_ClassKey == studentSubjectClass.Training_Subject_ClassKey
                                             && scoreTransfer.Training_Subject_ClassKey == subjectClass.Training_Subject_ClassKey
                                             && scoreTransfer.Training_TrainingProgramKeyOld == studentSubjectClass.Training_TrainingProgramKey
                                             && Training_Score.Contains(subjectClass.Training_Subject_ClassKey)
                                       select new
                                       {
                                           scoreTransfer.Training_StudentKey,
                                           scoreTransfer.ID,
                                           scoreTransfer.LastName,
                                           scoreTransfer.SureName,
                                           scoreTransfer.Gender,
                                           scoreTransfer.Birthday,
                                           scoreTransfer.CellPhone,
                                           scoreTransfer.Email,
                                           scoreTransfer.Training_TrainingProgramKey,
                                           subjectClass.Training_Subject_ClassKey,
                                           subjectClass.Training_SubjectKey,
                                           studentSubjectClass.AverageScoreFinal,
                                           scoreTransfer.Yearly,
                                           scoreTransfer.Training_ClassKey,
                                           scoreTransfer.Catalogue_MajorKey

                                       };
            //ListStudentAndScore = from listStudentAndScore in ListStudentAndScore
            ListStudentAndScore = ListStudentAndScore.Union(ListStudentAndScore1).OrderByDescending(p => p.Training_Subject_ClassKey);

            return ListStudentAndScore;

        }


        #region Phát triển bởi Hồ Tấn Đạt
        /// <summary>
        /// 
        /// </summary>
        /// <param name="_iStageKey"></param>
        /// <param name="_iDeparmentKey"></param>
        /// <param name="_iMajorKey"></param>
        /// <param name="_iCourseKey"></param>
        /// <param name="_iLevelKey"></param>
        /// <param name="_iTrainingTypeKey"></param>
        /// <returns></returns>
        public static IQueryable GetListByRef(ref  bool check, int _iStageKey, int _iDeparmentKey = -1, int _iMajorKey = -1, int _iCourseKey = -1, int _iLevelKey = -1, int _iTrainingTypeKey = -1)
        {
            DBMain data = new DBMain();
            var result = from std_cls in data.m_DCReadOnly.Training_Student_Classes
                         from std in data.m_DCReadOnly.Training_Students
                         from dpm in data.m_DCReadOnly.Catalogue_Departments
                         from mj in data.m_DCReadOnly.Catalogue_Majors
                         from cls in data.m_DCReadOnly.Training_Classes
                         where std_cls.Training_StudentKey == std.Training_StudentKey &&
                         std_cls.Training_ClassKey == cls.Training_ClassKey &&
                         cls.Catalogue_DepartmentKey == dpm.Catalogue_DepartmentKey &&
                         std_cls.Catalogue_MajorKey == mj.Catalogue_MajorKey
                         && std_cls.Catalogue_StatusKey == 1
                         select new
                         {
                             std.Training_StudentKey,
                             dpm.Catalogue_DepartmentKey,
                             mj.Catalogue_MajorKey,
                             cls.Catalogue_LevelKey,
                             std_cls.Catalogue_CourseKey,
                             std_cls.Catalogue_TrainingTypeKey,
                             FullName = std.LastName + " " + std.SureName,
                             std_cls.ID,
                             std.Gender,
                             std.Birthday,
                             std.PlaceOfBirth,
                             DeparmentName = dpm.Name,
                             MajorName = mj.Name,
                             ClassName = cls.Name,
                             cls.Yearly,
                             std_cls.Training_TrainingProgramKey

                         };

            if (_iCourseKey != -1) { result = result.Where(p => p.Catalogue_CourseKey == _iCourseKey); }
            if (_iMajorKey != -1) { result = result.Where(p => p.Catalogue_MajorKey == _iMajorKey); }
            if (_iDeparmentKey != -1) { result = result.Where(p => p.Catalogue_DepartmentKey == _iDeparmentKey); }
            if (_iLevelKey != -1) { result = result.Where(p => p.Catalogue_LevelKey == _iLevelKey); }
            if (_iTrainingTypeKey != -1) { result = result.Where(p => p.Catalogue_TrainingTypeKey == _iTrainingTypeKey); }

            var checkData = from p in result
                            where (from gsr in data.m_DCReadOnly.Graduate_Result_Considers
                                   where gsr.Catalogue_StageKey == _iStageKey
                                   select gsr.Training_StudentKey).Contains(p.Training_StudentKey)
                            select p;

            if (checkData.FirstOrDefault() != null)
            {
                check = false;
            }
            else
            {
                check = true;
            }

            //result = from p in result
            //         where !(from gsr in data.m_DCReadOnly.Graduate_Student_Registers
            //                 where gsr.Catalogue_StageKey == _iStageKey
            //                 select gsr.Training_StudentKey).Contains(p.Training_StudentKey)
            //         select p;


            return result;
        }

        #endregion

        #region [An]
        /// <summary>
        /// Lấy thông tin sinh viên
        /// </summary>
        /// <param name="nTraining_Student"></param>
        /// <returns></returns>
        /// 
        public static IQueryable GetData(this Table<Training_Student> nTraining_Student)
        {
            var values = from t in nTraining_Student
                         select new
                         {
                             t.Training_StudentKey,
                             t.LastName,
                             t.SureName,
                             Name = t.LastName + " " + t.SureName
                         };
            return values.Distinct();
        }

        /// <summary>
        /// Lấy danh sách sinh viên thuộc ngành, loại hình đào tạo, khóa học, bậc đào tạo, lớp
        /// </summary>
        /// <param name="trnStudentClass"></param>
        /// <param name="trnStudent"></param>
        /// <param name="m_iMajorKey"></param>
        /// <param name="m_iTrainingTypeKey"></param>
        /// <param name="m_iCourseKey"></param>
        /// <param name="m_iLevelKey"></param>
        /// <param name="m_iClassKey"></param>
        /// <returns></returns>

        public static IQueryable GetStudentInClassByMajorTrainingTypeCourseLevel(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Class> nTraining_Student_Class,
            int m_iMajorKey,
            int m_iTrainingTypeKey,
            int m_iCourseKey,
            int m_iLevelKey,
            int m_iClassKey)
        {
            var studentList = from t in nTraining_Student_Class
                              join t1 in nTraining_Student on t.Training_StudentKey equals t1.Training_StudentKey
                              where t.Catalogue_MajorKey == m_iMajorKey &&
                              t.Catalogue_TrainingTypeKey == m_iTrainingTypeKey &&
                              t.Catalogue_CourseKey == m_iCourseKey &&
                              t.Catalogue_LevelKey == m_iLevelKey &&
                              t.Training_ClassKey == m_iClassKey &&
                              t.Catalogue_StatusKey != null &&
                              t.Mainly == true
                              select new
                              {
                                  t.Training_StudentKey,
                                  t.ID,
                                  t1.LastName,
                                  t1.SureName,
                                  t1.Birthday,
                                  Gender = t1.Gender == true ? "Nữ" : "Nam",
                                  t1.Note,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_StatusKey
                              };
            return studentList.Distinct();
        }

        /// <summary>
        /// Lấy danh sách sinh viên chưa xếp lớp (classKey = null) thuộc ngành, khoa, loại hình đào tạo, khóa học, bậc đào tạo
        /// </summary>
        /// <param name="trnStudentClass"></param>
        /// <param name="trnStudent"></param>
        /// <param name="m_iMajorKey"></param>
        /// <param name="m_iTrainingTypeKey"></param>
        /// <param name="m_iCourseKey"></param>
        /// <param name="m_iLevelKey"></param>
        /// <returns></returns>

        public static IQueryable GetStudentNotInClassByMajorTrainingTypeCourseLevel(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Class> nTraining_Student_Class,
            int m_iMajorKey,
            int m_iTrainingTypeKey,
            int m_iCourseKey,
            int m_iLevelKey)
        {
            var studentList = from t in nTraining_Student_Class
                              join t1 in nTraining_Student on t.Training_StudentKey equals t1.Training_StudentKey
                              where t.Catalogue_MajorKey == m_iMajorKey &&
                              t.Catalogue_TrainingTypeKey == m_iTrainingTypeKey &&
                              t.Catalogue_CourseKey == m_iCourseKey &&
                              t.Catalogue_LevelKey == m_iLevelKey &&
                              t.Training_ClassKey == null &&
                              t.Mainly == true
                              select new
                              {
                                  t.Training_StudentKey,
                                  t.ID,
                                  t1.LastName,
                                  t1.SureName,
                                  t1.Birthday,
                                  Gender = t1.Gender == true ? "Nữ" : "Nam",
                                  t1.Note,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_StatusKey
                              };
            return studentList.Distinct();
        }

        /// <summary>
        /// Danh sách sinh viên đã đăng ký môn A
        /// </summary>
        /// <param name="n_tso"></param>
        /// <param name="n_ttp"></param>
        /// <param name="n_ttpd"></param>
        /// <param name="n_tsc"></param>
        /// <param name="n_tc"></param>
        /// <param name="n_ts"></param>
        /// <param name="n_tss"></param>
        /// <param name="m_iTraining_SubjectKey"></param>
        /// <returns></returns>

        public static IQueryable GetStudentListRegistSubject(this Table<Training_Student> nTraining_Student,
            Table<Training_Subject_Open> nTraining_Subject_Open,
            Table<Training_Student_Class> nTraining_Student_Class,
            Table<Training_Student_Subject> nTraining_Student_Subject,
            int m_iTraining_SubjectKey,
            string m_strStudentID,
            int m_iTrainingProgramKey,
            int m_iTraining_SubjectOpenKey)
        {
            // DANH SÁCH SINH VIÊN ĐĂNG KÝ MÔN A TRONG CHƯƠNG TRÌNH ĐÀO TẠO
            var values = from t in nTraining_Student_Subject
                         join t1 in nTraining_Subject_Open on t.Training_SubjectOpenKey equals t1.Training_SubjectOpenKey
                         join t2 in nTraining_Student on t.Training_StudentKey equals t2.Training_StudentKey
                         join t3 in nTraining_Student_Class on t2.Training_StudentKey equals t3.Training_StudentKey
                         where t.Training_TrainingProgramKey == m_iTrainingProgramKey &&
                         t1.Training_SubjectKey == m_iTraining_SubjectKey &&
                         t1.Training_SubjectOpenKey == m_iTraining_SubjectOpenKey &&    // Danh sách sinh viên đăng ký môn đang mở
                         t.Training_Student_Subject_Regist_TypeKey == 0 &&              // ĐĂNG KÝ LẦN ĐẦU
                         t3.ID != null &&
                         t3.Mainly == true
                         select new
                         {
                             t3.Catalogue_MajorKey,
                             t3.Training_ClassKey,
                             t.Training_StudentKey,
                             t3.ID,
                             t2.LastName,
                             t2.SureName,
                             t2.Birthday,
                             Gender = t2.Gender == true ? "Nữ" : "Nam",
                             t3.Catalogue_StatusKey
                         };
            if (m_strStudentID != "")
            {
                values = values.Where(tb => tb.ID.Contains(m_strStudentID));
            }
            return values.Distinct();
        }

        /// <summary>
        /// Danh sách sinh viên chưa đăng ký môn A
        /// Điều kiện :
        ///     - Môn có trong CTĐT của sinh viên
        ///     - Chưa đăng ký môn A
        /// </summary>
        /// <param name="nTraining_Student"></param>
        /// <param name="nTraining_TrainingProgram_Detail"></param>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="nTraining_Student_Subject"></param>
        /// <param name="m_iTraining_SubjectKey"></param>
        /// <param name="m_iTraining_SubjectOpenKey"></param>
        /// <param name="m_strStudentID"></param>
        /// <param name="m_iTrainingProgramKey"></param>
        /// <returns></returns>

        public static IQueryable GetStudentListNotRegistSubject(this Table<Training_Student> nTraining_Student,
            Table<Training_TrainingProgram_Detail> nTraining_TrainingProgram_Detail,
            Table<Training_Student_Class> nTraining_Student_Class,
            Table<Training_Student_Subject> nTraining_Student_Subject,
            Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,
            Table<Training_Subject_Class> nTraining_Subject_Class,
            int m_iTraining_SubjectKey,
            int m_iTraining_SubjectOpenKey,
            string m_strStudentID = "",
            int m_iTrainingProgramKey = 0)
        {
            // DANH SÁCH SINH VIÊN CÓ MÔN A TRONG CHƯƠNG TRÌNH ĐÀO TẠO
            var values = from t in nTraining_Student_Class
                         join t1 in nTraining_TrainingProgram_Detail on t.Training_TrainingProgramKey equals t1.Training_TrainingProgramKey
                         join t2 in nTraining_Student on t.Training_StudentKey equals t2.Training_StudentKey
                         where t.Training_TrainingProgramKey == m_iTrainingProgramKey &&
                         t.ID != null &&
                         t.Mainly == true &&
                         t1.Training_SubjectKey == m_iTraining_SubjectKey
                         select new
                         {
                             t.Catalogue_MajorKey,
                             t.Training_ClassKey,
                             t.Training_StudentKey,
                             t.ID,
                             t2.LastName,
                             t2.SureName,
                             t2.Birthday,
                             Gender = t2.Gender == true ? "Nữ" : "Nam",
                             t.Catalogue_StatusKey
                         };

            // Danh sách sinh viên đã đăng ký môn A
            var studentsubject = from t in nTraining_Student_Subject
                                 where t.Training_TrainingProgramKey == m_iTrainingProgramKey
                                 where t.Training_SubjectKey == m_iTraining_SubjectKey
                                 select new
                                 {
                                     t.Training_StudentKey
                                 };

            // Danh sách sinh viên đã xếp lớp môn A
            var studentclass = from t in nTraining_Student_Subject_Class
                               join t1 in nTraining_Subject_Class on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                               where t1.Training_SubjectKey == m_iTraining_SubjectKey
                               select new
                               {
                                   t.Training_StudentKey
                               };

            // Danh sách sinh viên đã đăng ký và xếp lớp môn A
            var condition1 = from t in studentsubject
                             join t1 in studentclass on t.Training_StudentKey equals t1.Training_StudentKey
                             select new
                             {
                                 t.Training_StudentKey
                             };

            // Loại sinh viên đã đăng ký và xếp lớp môn A
            var result = from t in values
                         where !(from t1 in condition1 select t1.Training_StudentKey).Contains(t.Training_StudentKey)
                         select t;

            // Danh sách sinh viên đăng ký môn A với mã Open key
            var stdsubjectOpen = from t in nTraining_Student_Subject
                                 where t.Training_SubjectOpenKey == m_iTraining_SubjectOpenKey
                                 select new
                                 {
                                     t.Training_StudentKey,
                                 };

            // Loại sinh viên vừa đăng ký môn A
            var result1 = from t in result
                          where
                          !(from t1 in stdsubjectOpen
                            select t1.Training_StudentKey).Contains(t.Training_StudentKey)
                          select t;

            // Chỉ lấy sinh viên nếu có tìm theo mã ID
            if (m_strStudentID != "")
            {
                result1 = result1.Where(tb => tb.ID.Contains(m_strStudentID));
            }
            return result1.Distinct();
        }


        /// <summary>
        /// Lấy danh sách sinh viên không đăng ký học lại hoặc cải thiện điểm
        /// </summary>
        /// <param name="nTraining_Student"></param>
        /// <param name="nTraining_Student_Subject_Class"></param>
        /// <param name="nTraining_Subject_Class"></param>
        /// <param name="nTraining_Class"></param>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="nTraining_Student_Subject"></param>
        /// <param name="m_iTraining_SubjectKey"></param>
        /// <param name="m_iTraining_SubjectOpenKey"></param>
        /// <param name="m_iTraining_TrainingProgramKey"></param>
        /// <param name="m_iFlag"></param>
        /// <returns></returns>

        public static IQueryable GetListStudentNotRegistSubjectAgaint(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,
            Table<Training_Subject_Class> nTraining_Subject_Class,
            Table<Training_Class> nTraining_Class,
            Table<Training_Student_Class> nTraining_Student_Class,
            Table<Training_Student_Subject> nTraining_Student_Subject,
            int m_iTraining_SubjectKey,
            int m_iTraining_SubjectOpenKey,
            int m_iTraining_TrainingProgramKey,
            int m_iFlag)
        {
            // danh sách sinh viên đăng ký môn A
            var studentsubject = from t in nTraining_Student_Subject_Class
                                 join t0 in nTraining_Subject_Class on t.Training_Subject_ClassKey equals t0.Training_Subject_ClassKey
                                 where t.Training_TrainingProgramKey == m_iTraining_TrainingProgramKey &&
                                 t0.Training_SubjectKey == m_iTraining_SubjectKey
                                 group new { t, t0 } by new
                                 {
                                     t.Training_StudentKey,
                                     t0.Training_SubjectKey,
                                     t0.Yearly
                                 } into g
                                 select new
                                 {
                                     Training_Subject_ClassKey = (System.Int32?)g.Max(p => p.t.Training_Subject_ClassKey),
                                     g.Key.Training_StudentKey,
                                     Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                                     g.Key.Yearly
                                 };
            // điểm sinh viên
            var studentscore = from t in nTraining_Student_Subject_Class
                               join t1 in nTraining_Subject_Class on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                               where t.Training_TrainingProgramKey == m_iTraining_TrainingProgramKey &&
                                 t1.Training_SubjectKey == m_iTraining_SubjectKey
                               select new
                               {
                                   t.Training_Subject_ClassKey,
                                   t.Training_StudentKey,
                                   t.AverageScore,
                                   t.AverageScoreFinal
                               };
            // điểm sinh viên đăng ký môn A
            var studentsubjectscore = from t in studentsubject
                                      join t1 in studentscore on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                                      where t.Training_StudentKey == t1.Training_StudentKey
                                      select new
                                      {
                                          t.Training_Subject_ClassKey,
                                          t.Training_StudentKey,
                                          t.Training_SubjectKey,
                                          t1.AverageScoreFinal,
                                          t.Yearly,
                                          Pass = t.Yearly == true ? (t1.AverageScoreFinal >= UniMana.Utils.Configs.DefaultConfig.MinScorePass_Yearly ? 1 : 0) : (t1.AverageScoreFinal >= UniMana.Utils.Configs.DefaultConfig.MinScorePass_NoYearly ? 1 : 0)
                                      };
            // danh sách sinh viên trong lớp
            var studentclass = from t in nTraining_Class
                               join t1 in nTraining_Student_Class on t.Training_ClassKey equals t1.Training_ClassKey
                               join t2 in nTraining_Student on t1.Training_StudentKey equals t2.Training_StudentKey
                               where t1.Training_TrainingProgramKey == m_iTraining_TrainingProgramKey &&
                               t1.Mainly == true
                               select new
                               {
                                   t1.Training_StudentKey,
                                   t1.ID,
                                   t2.LastName,
                                   t2.SureName,
                                   t2.Birthday,
                                   t2.Gender,
                                   t1.Training_ClassKey
                               };
            // danh sách sinh viên đã đăng ký học lại hoặc học cải thiện
            var studentregist = from t in nTraining_Student_Subject
                                where t.Training_Student_Subject_Regist_TypeKey == m_iFlag &&
                                t.Training_SubjectKey == m_iTraining_SubjectKey &&
                                t.Training_SubjectOpenKey == m_iTraining_SubjectOpenKey &&
                                t.Training_TrainingProgramKey == m_iTraining_TrainingProgramKey
                                select new
                                {
                                    t.Training_Student_SubjectKey,
                                    t.Training_StudentKey,
                                    t.Training_SubjectKey,
                                    t.Training_Student_Subject_Regist_TypeKey,
                                    t.Training_TrainingProgramKey
                                };

            // Lấy kết quả lớp sinh viên và điểm đậu
            if (m_iFlag == 1)
            {
                var values1 = from t in studentsubjectscore
                              join t1 in studentclass on t.Training_StudentKey equals t1.Training_StudentKey
                              where t.Pass == 0 &&
                              t.AverageScoreFinal != null
                              select new
                              {
                                  t.Training_Subject_ClassKey,
                                  t.Training_StudentKey,
                                  t.AverageScoreFinal,
                                  t1.ID,
                                  t1.LastName,
                                  t1.SureName,
                                  t1.Birthday,
                                  Gender = t1.Gender == true ? "Nữ" : "Nam",
                                  t1.Training_ClassKey
                              };
                var result = from t in values1
                             where !(from t1 in studentregist select t1.Training_StudentKey).Contains(t.Training_StudentKey)
                             select new
                             {
                                 t.Training_Subject_ClassKey,
                                 t.Training_StudentKey,
                                 t.AverageScoreFinal,
                                 t.ID,
                                 t.LastName,
                                 t.SureName,
                                 t.Birthday,
                                 t.Gender,
                                 t.Training_ClassKey
                             };
                return result;
            }
            else
            {
                var values2 = from t in studentsubjectscore
                              join t1 in studentclass on t.Training_StudentKey equals t1.Training_StudentKey
                              where t.Pass == 1 &&
                              t.AverageScoreFinal <= 6 &&
                              t.AverageScoreFinal != null
                              select new
                              {
                                  t.Training_Subject_ClassKey,
                                  t.Training_StudentKey,
                                  t.AverageScoreFinal,
                                  t1.ID,
                                  t1.LastName,
                                  t1.SureName,
                                  t1.Birthday,
                                  Gender = t1.Gender == true ? "Nữ" : "Nam",
                                  t1.Training_ClassKey
                              };
                var result = from t in values2
                             where !(from t1 in studentregist select t1.Training_StudentKey).Contains(t.Training_StudentKey)
                             select new
                             {
                                 t.Training_Subject_ClassKey,
                                 t.Training_StudentKey,
                                 t.AverageScoreFinal,
                                 t.ID,
                                 t.LastName,
                                 t.SureName,
                                 t.Birthday,
                                 t.Gender,
                                 t.Training_ClassKey
                             };
                return result;
            }
        }

        /// <summary>
        /// Lấy danh sách sinh viên đã đăng ký học lại
        /// </summary>
        /// <param name="nTraining_Student"></param>
        /// <param name="nTraining_Student_Subject"></param>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="m_iTraining_SubjectKey"></param>
        /// <param name="m_iTraining_SubjectOpenKey"></param>
        /// <param name="m_iTraining_ProgramKey"></param>
        /// <param name="m_iFlag"></param>
        /// <returns></returns>

        public static IQueryable GetListStudentRegistSubjectAgaint(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Subject> nTraining_Student_Subject,
            Table<Training_Student_Class> nTraining_Student_Class,
            int m_iTraining_SubjectKey,
            int m_iTraining_SubjectOpenKey,
            int m_iTraining_ProgramKey,
            int m_iFlag)
        {
            var studentregist = from t in nTraining_Student_Subject
                                where t.Training_Student_Subject_Regist_TypeKey == m_iFlag &&
                                t.Training_SubjectKey == m_iTraining_SubjectKey &&
                                t.Training_SubjectOpenKey == m_iTraining_SubjectOpenKey &&
                                t.Training_TrainingProgramKey == m_iTraining_ProgramKey
                                select new
                                {
                                    t.Training_Student_SubjectKey,
                                    t.Training_StudentKey,
                                    t.Training_SubjectKey,
                                    t.Training_Student_Subject_Regist_TypeKey,
                                    t.Training_TrainingProgramKey
                                };

            var result = from t in studentregist
                         join t0 in nTraining_Student on t.Training_StudentKey equals t0.Training_StudentKey
                         join t1 in nTraining_Student_Class on t.Training_StudentKey equals t1.Training_StudentKey
                         select new
                         {
                             t.Training_StudentKey,
                             t1.ID,
                             t0.LastName,
                             t0.SureName,
                             t0.Birthday,
                             Gender = t0.Gender == true ? "Nữ" : "Nam",
                             t1.Training_ClassKey
                         };
            return result;
        }

        /// <summary>
        /// Lấy danh sách sinh viên đã xếp lớp còn học
        /// </summary>
        /// <param name="nTraining_Student"></param>
        /// <param name="nTraining_Student_Class"></param>
        /// <returns></returns>

        public static IQueryable GetIDNameStudent(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Class> nTraining_Student_Class)
        {
            var values = from t in nTraining_Student
                         join t1 in nTraining_Student_Class on t.Training_StudentKey equals t1.Training_StudentKey
                         orderby
                            t1.ID
                         where t1.Catalogue_StatusKey == 1
                         select new
                         {
                             t.Training_StudentKey,
                             t1.ID,
                             t.LastName,
                             t.SureName
                         };
            return values.Distinct();
        }
        #endregion

        /// <summary>
        /// Lấy mã sinh viên
        /// </summary>
        /// <param name="std"></param>
        /// <param name="stdcls"></param>
        /// <returns></returns>

        public static IQueryable GetStudentKeyID(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Class> nTraining_Student_Class)
        {
            var value = from s in nTraining_Student
                        join c in nTraining_Student_Class on s.Training_StudentKey equals c.Training_StudentKey
                        where c.ID != null
                        where c.Training_ClassKey != null
                        where c.Mainly == true
                        select new
                        {
                            c.Training_StudentKey,
                            c.ID
                        };
            return value.Distinct();
        }

        /// <summary>
        /// Lấy mã sinh viên với mã lớp học phần
        /// </summary>
        /// <param name="std"></param>
        /// <param name="stdcls"></param>
        /// <param name="stdsubcls"></param>
        /// <returns></returns>

        public static IQueryable GetStudentKeyIDByClassKey(this Table<Training_Student> nTraining_Student,
            Table<Training_Student_Class> nTraining_Student_Class,
            Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,
            int m_iClassKey)
        {
            var value = from t in nTraining_Student_Subject_Class
                        join t0 in nTraining_Student_Class on t.Training_StudentKey equals t0.Training_StudentKey
                        join t1 in nTraining_Student on t.Training_StudentKey equals t1.Training_StudentKey
                        where
                          t.Training_Subject_ClassKey == m_iClassKey &&
                          t0.Catalogue_StatusKey == 1 &&
                          t0.Mainly == true
                        select new
                        {
                            t0.Training_StudentKey,
                            t0.ID
                        };
            return value.Distinct();
        }
    }
}
